﻿using Dapper;
using SqlKata;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;

namespace Kele.Repository
{
    public static class AppRepository
    {
        #region FirstOrDefault

        public static async Task<T> FirstOrDefaultAsync<T>(int id, string tableName = "", string cacheKey = "")
        {
            return await FirstOrDefaultAsync<T>("Id", id, tableName, cacheKey);
        }

        public static async Task<T> FirstOrDefaultAsync<T>(string column, object value, string tableName = "", string cacheKey = "")
        {
            var query = new Query().Where(column, value);
            return await FirstOrDefaultAsync<T>(query, tableName, cacheKey);
        }

        public static async Task<T> FirstOrDefaultAsync<T>(Query query, string tableName = "", string cacheKey = "")
        {
            if (string.IsNullOrWhiteSpace(tableName))
            {
                tableName = AppReflection.GetTableName<T>();
            }
            query = query == null ? new Query(tableName) : query.From(tableName);
            return await AppCache.GetOrCreateAsync(cacheKey,
                      async () =>
                      {
                          using var db = new Database();
                          var compiled = db.Compile(query);
                          return await db.Connection.QueryFirstAsync<T>(compiled.Sql, compiled.NamedBindings);
                      }
                  );
        }

        #endregion FirstOrDefault

        #region Get

        public static async Task<List<T>> GetAsync<T>(int id, string tableName = "", string cacheKey = "")
        {
            return await GetAsync<T>("Id", id, tableName, cacheKey);
        }

        public static async Task<List<T>> GetAsync<T>(string column, object value, string tableName = "", string cacheKey = "")
        {
            var query = new Query().Where(column, value);
            return await GetAsync<T>(tableName, query, tableName, cacheKey);
        }

        public static async Task<List<T>> GetAsync<T>(Query query = null, string tableName = "", string cacheKey = "")
        {
            if (string.IsNullOrWhiteSpace(tableName))
            {
                tableName = AppReflection.GetTableName<T>();
            }

            query = query == null ? new Query(tableName) : query.From(tableName);
            return await AppCache.GetOrCreateAsync(cacheKey, async () =>
            {
                using var db = new Database();
                var compiled = db.Compile(query);
                var list = await db.Connection.QueryAsync<T>(compiled.Sql, compiled.NamedBindings);
                return list != null ? list.ToList() : new List<T>();
            });
        }

        #endregion Get

        #region Paginate

        public static async Task<PaginationResult<T>> PaginateAsync<T>(int skip, int take, Query query = null, string tableName = "")
        {
            if (string.IsNullOrWhiteSpace(tableName))
            {
                tableName = AppReflection.GetTableName<T>();
            }
            query = query == null ? new Query(tableName) : query.From(tableName);
            var perPage = take;
            var page = (int)Math.Floor((double)skip / take) + 1;
            using var db = new Database();

            var countQuery = query.Clone().AsCount();
            var countCompiled = db.Compile(countQuery);
            var count = await db.Connection.ExecuteScalarAsync<long>(countCompiled.Sql, countCompiled.NamedBindings);

            IEnumerable<T> list;

            if (count > 0)
            {
                var pageQuery = query.Clone().Skip(skip).Take(take);
                var pageCompiled = db.Compile(pageQuery);
                list = await db.Connection.QueryAsync<T>(pageCompiled.Sql, pageCompiled.NamedBindings);
            }
            else
            {
                list = Enumerable.Empty<T>();
            }
            return new PaginationResult<T>
            {
                Count = count,
                List = list
            };
        }

        #endregion Paginate

        #region insert

        public static async Task<TResult> InsertGetIdAsync<T, TResult>(IReadOnlyDictionary<string, object> data, string tableName = "")
        {
            var query = new Query(tableName).AsInsert(data, true);
            return await InsertGetIdAsync<T, TResult>(query, tableName);
        }

        public static async Task<TResult> InsertGetIdAsync<T, TResult>(object data, string tableName = "")
        {
            var query = new Query(tableName).AsInsert(data, true);
            return await InsertGetIdAsync<T, TResult>(query, tableName);
        }

        public static async Task<TResult> InsertGetIdAsync<T, TResult>(Query query, string tableName = "")
        {
            if (string.IsNullOrWhiteSpace(tableName))
            {
                tableName = AppReflection.GetTableName<T>();
            }
            using var db = new Database();
            var compiled = db.Compile(query.Clone().From(tableName));

            var row = await db.Connection.QueryFirstAsync<InsertGetIdRow<TResult>>(compiled.Sql, compiled.NamedBindings);
            return row.Id;
        }

        public static async Task<int> InsertAsync<T>(Query query, string tableName = "")
        {
            if (string.IsNullOrWhiteSpace(tableName))
            {
                tableName = AppReflection.GetTableName<T>();
            }
            using var db = new Database();
            var compiled = db.Compile(query.From(tableName));
            return await db.Connection.ExecuteScalarAsync<int>(compiled.Sql, compiled.NamedBindings);
        }


        #endregion insert

        #region update

        public static async Task<int> UpdateAsync<T>(int id, IReadOnlyDictionary<string, object> data, string tableName = "", string[] cacheKeysToRemove = null)
        {
            return await UpdateAsync<T>("Id", id, data, tableName, cacheKeysToRemove);
        }

        public static async Task<int> UpdateAsync<T>(string column, object value, IReadOnlyDictionary<string, object> data, string tableName = "", string[] cacheKeysToRemove = null)
        {
            var query = new Query().Where(column, value);
            return await UpdateAsync<T>(query, data, tableName, cacheKeysToRemove);
        }


         public static async Task<int> UpdateAsync<T>(Query query, object data, string tableName = "", string[] cacheKeysToRemove = null)
        {
            if (string.IsNullOrWhiteSpace(tableName))
            {
                tableName = AppReflection.GetTableName<T>();
            }
            query = query == null ? new Query(tableName) : query.From(tableName);
            await AppCache.RemoveAsync(cacheKeysToRemove);
            using var db = new Database();
            var compiled = db.Compile(query.AsUpdate(data));
            return await db.Connection.ExecuteAsync(compiled.Sql, compiled.NamedBindings);
        }

        public static async Task<int> UpdateAsync<T>(Query query, IReadOnlyDictionary<string, object> data, string tableName = "", string[] cacheKeysToRemove = null)
        {
            if (string.IsNullOrWhiteSpace(tableName))
            {
                tableName = AppReflection.GetTableName<T>();
            }
            query = query == null ? new Query(tableName) : query.From(tableName);
            await AppCache.RemoveAsync(cacheKeysToRemove);
            using var db = new Database();
            var compiled = db.Compile(query.AsUpdate(data));
            return await db.Connection.ExecuteAsync(compiled.Sql, compiled.NamedBindings);
        }

        #endregion update

        #region delete

        public static async Task<int> DeleteAsync<T>(int id, string tableName = "", string[] cacheKeysToRemove = null)
        {
            var query = new Query().Where("Id", id);
            return await DeleteAsync<T>(query, tableName, cacheKeysToRemove);
        }

        public static async Task<int> DeleteAsync<T>(Query query, string tableName = "", string[] cacheKeysToRemove = null)
        {
            if (string.IsNullOrWhiteSpace(tableName))
            {
                tableName = AppReflection.GetTableName<T>();
            }
            query = query == null ? new Query(tableName) : query.From(tableName);
            await AppCache.RemoveAsync(cacheKeysToRemove);
            using var db = new Database();
            var compiled = db.Compile(query.AsDelete());
            return await db.Connection.ExecuteAsync(compiled.Sql, compiled.NamedBindings);
        }

        #endregion delete
    }
}